require('dotenv').config();
const express = require('express');
const mysql = require('mysql2/promise');
const path = require('path');
const bodyParser = require('body-parser');
const bcrypt = require('bcryptjs');
const session = require('express-session');
const multer = require('multer');
const xlsx = require('xlsx');

const app = express();
const PORT = process.env.PORT || 3000;

// 中间件
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static(__dirname));
app.use(session({
    secret: process.env.SESSION_SECRET || 'student_management_secret',
    resave: false,
    saveUninitialized: false,
    cookie: { secure: false, maxAge: 24 * 60 * 60 * 1000 } // 24小时
}));

// 文件上传配置
const storage = multer.memoryStorage();
const upload = multer({
    storage: storage,
    fileFilter: (req, file, cb) => {
        if (file.mimetype === 'text/csv' ||
            file.mimetype === 'application/vnd.ms-excel' ||
            file.mimetype === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
            cb(null, true);
        } else {
            cb(new Error('只支持CSV和Excel文件'), false);
        }
    },
    limits: {
        fileSize: 5 * 1024 * 1024 // 5MB
    }
});

// RDS数据库配置 - 使用环境变量
const dbConfig = {
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT) || 3306,
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '123456',
    database: process.env.DB_NAME || 'sms',
    waitForConnections: true,
    connectionLimit: 20,
    acquireTimeout: 60000,
    timeout: 60000,
    reconnect: true,
    charset: 'utf8mb4',
    timezone: '+08:00'
};

// 创建数据库连接池
const pool = mysql.createPool(dbConfig);

// 添加连接池事件监听
pool.on('acquire', function (connection) {
    console.log('Connection %d acquired', connection.threadId);
});

pool.on('release', function (connection) {
    console.log('Connection %d released', connection.threadId);
});

pool.on('enqueue', function () {
    console.log('Waiting for available connection slot');
});

// 数据库连接健康检查函数
async function checkDatabaseConnection() {
    try {
        const connection = await pool.getConnection();
        console.log('RDS数据库连接成功');
        connection.release();
        return true;
    } catch (error) {
        console.error('RDS数据库连接失败:', error.message);
        return false;
    }
}

// 统一的数据库查询包装函数
async function executeQuery(sql, params = []) {
    let connection;
    try {
        connection = await pool.getConnection();
        const [results] = await connection.execute(sql, params);
        return results;
    } catch (error) {
        console.error('数据库查询错误:', error);

        // 针对跨可用区连接的特殊错误处理
        if (error.code === 'ECONNREFUSED' || error.code === 'ETIMEDOUT') {
            console.error('网络连接问题，可能是跨可用区延迟');
            throw new Error('数据库连接超时，请稍后重试');
        }

        throw error;
    } finally {
        if (connection) {
            connection.release();
        }
    }
}

// 增强的数据库初始化函数
async function initializeDatabaseWithRetry(maxRetries = 3, retryDelay = 5000) {
    for (let attempt = 1; attempt <= maxRetries; attempt++) {
        try {
            console.log(`尝试连接数据库 (第 ${attempt} 次)...`);
            await initializeDatabase();
            console.log('数据库初始化成功');
            return;
        } catch (error) {
            console.error(`数据库初始化失败 (第 ${attempt} 次):`, error.message);

            if (attempt === maxRetries) {
                console.error('达到最大重试次数，请检查RDS配置');
                throw error;
            }

            console.log(`等待 ${retryDelay/1000} 秒后重试...`);
            await new Promise(resolve => setTimeout(resolve, retryDelay));
        }
    }
}

// 初始化数据库表
async function initializeDatabase() {
    let connection;
    try {
        connection = await pool.getConnection();

        // 创建用户表
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS user (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(255) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                name VARCHAR(255) NOT NULL,
                role_id INT NOT NULL,
                create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        `);

        // 创建学生信息表
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS student (
                id INT AUTO_INCREMENT PRIMARY KEY,
                student_id VARCHAR(50) UNIQUE NOT NULL,
                name VARCHAR(255) NOT NULL,
                gender ENUM('男', '女') NOT NULL,
                class_name VARCHAR(100) NOT NULL,
                major VARCHAR(100) NOT NULL,
                create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        `);

        // 创建教师信息表
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS teacher (
                id INT AUTO_INCREMENT PRIMARY KEY,
                teacher_id VARCHAR(50) UNIQUE NOT NULL,
                name VARCHAR(255) NOT NULL,
                department VARCHAR(100) NOT NULL,
                create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        `);

        // 创建成绩表
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS score (
                id INT AUTO_INCREMENT PRIMARY KEY,
                student_id VARCHAR(50) NOT NULL,
                student_name VARCHAR(255) NOT NULL,
                course_name VARCHAR(255) NOT NULL,
                score DECIMAL(5,2) NOT NULL,
                semester VARCHAR(50) NOT NULL,
                create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE
            )
        `);

        // 创建查询设置表
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS query_settings (
               id INT AUTO_INCREMENT PRIMARY KEY,
               start_time DATETIME NOT NULL,
               end_time DATETIME NOT NULL,
               created_by INT NOT NULL,
               create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
               update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
               FOREIGN KEY (created_by) REFERENCES user(id) ON DELETE CASCADE
            )
        `);

        // 检查是否已有示例数据
        const userRows = await executeQuery('SELECT COUNT(*) as count FROM user');
        const studentRows = await executeQuery('SELECT COUNT(*) as count FROM student');
        const teacherRows = await executeQuery('SELECT COUNT(*) as count FROM teacher');
        const scoreRows = await executeQuery('SELECT COUNT(*) as count FROM score');

        if (userRows[0].count === 0) {
            // 插入示例用户数据
            const hashedPassword = bcrypt.hashSync('123456', 10);

            // 管理员
            await executeQuery(
                'INSERT INTO user (username, password, name, role_id) VALUES (?, ?, ?, ?)',
                ['1001', hashedPassword, '系统管理员', 3]
            );
            // 教师
            await executeQuery(
                'INSERT INTO user (username, password, name, role_id) VALUES (?, ?, ?, ?)',
                ['2001', hashedPassword, '张老师', 1]
            );
            // 学生
            await executeQuery(
                'INSERT INTO user (username, password, name, role_id) VALUES (?, ?, ?, ?)',
                ['2025001', hashedPassword, '李小明', 2]
            );
            console.log('示例用户数据插入成功');
        }

        if (studentRows[0].count === 0) {
            // 插入示例学生数据
            await executeQuery(
                'INSERT INTO student (student_id, name, gender, class_name, major) VALUES (?, ?, ?, ?, ?)',
                ['2025001', '李小明', '男', '计算机科学与技术1班', '计算机科学与技术']
            );
            await executeQuery(
                'INSERT INTO student (student_id, name, gender, class_name, major) VALUES (?, ?, ?, ?, ?)',
                ['2025002', '王小红', '女', '软件工程1班', '软件工程']
            );
            await executeQuery(
                'INSERT INTO student (student_id, name, gender, class_name, major) VALUES (?, ?, ?, ?, ?)',
                ['2025003', '张三', '男', '网络工程1班', '网络工程']
            );
            console.log('示例学生数据插入成功');
        }

        if (teacherRows[0].count === 0) {
            // 插入示例教师数据
            await executeQuery(
                'INSERT INTO teacher (teacher_id, name, department) VALUES (?, ?, ?)',
                ['2001', '张老师', '计算机学院']
            );
            await executeQuery(
                'INSERT INTO teacher (teacher_id, name, department) VALUES (?, ?, ?, ?)',
                ['2002', '王老师', '软件学院']
            );
            console.log('示例教师数据插入成功');
        }

        if (scoreRows[0].count === 0) {
            // 插入示例成绩数据
            await executeQuery(
                'INSERT INTO score (student_id, student_name, course_name, score, semester) VALUES (?, ?, ?, ?, ?)',
                ['2025001', '李小明', '数据结构', 85.5, '2024-2025学年第一学期']
            );
            await executeQuery(
                'INSERT INTO score (student_id, student_name, course_name, score, semester) VALUES (?, ?, ?, ?, ?)',
                ['2025001', '李小明', '操作系统', 92.0, '2024-2025学年第一学期']
            );
            await executeQuery(
                'INSERT INTO score (student_id, student_name, course_name, score, semester) VALUES (?, ?, ?, ?, ?)',
                ['2025002', '王小红', '数据结构', 88.0, '2024-2025学年第一学期']
            );
            console.log('示例成绩数据插入成功');
        }

        console.log('数据库初始化成功');
    } catch (error) {
        console.error('数据库初始化失败:', error);
        throw error; // 重新抛出错误以便重试机制捕获
    } finally {
        if (connection) {
            connection.release();
        }
    }
}

// 启动时初始化数据库
initializeDatabaseWithRetry().catch(error => {
    console.error('数据库初始化最终失败:', error);
});

// 定期健康检查（每5分钟）
setInterval(async () => {
    await checkDatabaseConnection();
}, 300000);

// 检查登录状态的中间件
function requireLogin(req, res, next) {
    if (!req.session.user) {
        return res.redirect('/');
    }
    next();
}

// 检查教师或管理员权限的中间件
function requireTeacherOrAdmin(req, res, next) {
    if (req.session.user.role_id !== 1 && req.session.user.role_id !== 3) {
        return res.json({ success: false, message: '权限不足' });
    }
    next();
}

// 注册接口
app.post('/api/register', async (req, res) => {
    const { username, password, name, role } = req.body;

    if (!username || !password || !name || !role) {
        return res.json({ success: false, message: '所有字段都是必需的' });
    }

    try {
        // 检查用户名是否已存在
        const rows = await executeQuery(
            'SELECT * FROM user WHERE username = ?',
            [username]
        );

        if (rows.length > 0) {
            return res.json({ success: false, message: '用户名已存在' });
        }

        // 加密密码
        const hashedPassword = bcrypt.hashSync(password, 10);

        // 插入新用户
        await executeQuery(
            'INSERT INTO user (username, password, name, role_id) VALUES (?, ?, ?, ?)',
            [username, hashedPassword, name, parseInt(role)]
        );

        // 如果是学生角色，同时在student表中插入记录
        if (parseInt(role) === 2) {
            try {
                await executeQuery(
                    'INSERT INTO student (student_id, name, gender, class_name, major) VALUES (?, ?, ?, ?, ?)',
                    [username, name, '男', '待分配', '待分配']
                );
            } catch (error) {
                console.log('学生信息插入失败，可能已存在:', error.message);
            }
        }

        // 如果是教师角色，同时在teacher表中插入记录
        if (parseInt(role) === 1) {
            try {
                await executeQuery(
                    'INSERT INTO teacher (teacher_id, name, department) VALUES (?, ?, ?)',
                    [username, name, '待分配']
                );
            } catch (error) {
                console.log('教师信息插入失败，可能已存在:', error.message);
            }
        }

        res.json({ success: true, message: '注册成功' });
    } catch (error) {
        console.error('注册错误:', error);
        res.json({ success: false, message: '注册失败: ' + error.message });
    }
});

// 登录接口
app.post('/api/login', async (req, res) => {
    const { username, password } = req.body;

    if (!username || !password) {
        return res.json({ success: false, message: '用户名和密码是必需的' });
    }

    try {
        // 查找用户
        const rows = await executeQuery(
            'SELECT * FROM user WHERE username = ?',
            [username]
        );

        if (rows.length === 0) {
            return res.json({ success: false, message: '用户不存在' });
        }

        const user = rows[0];

        // 验证密码
        const isPasswordValid = bcrypt.compareSync(password, user.password);
        if (!isPasswordValid) {
            return res.json({ success: false, message: '密码错误' });
        }

        // 存储用户信息到session
        req.session.user = {
            id: user.id,
            username: user.username,
            name: user.name,
            role_id: user.role_id
        };

        res.json({
            success: true,
            message: '登录成功',
            role_id: user.role_id,
            name: user.name
        });
    } catch (error) {
        console.error('登录错误:', error);
        res.json({ success: false, message: '登录失败: ' + error.message });
    }
});

// 登出接口
app.get('/api/logout', (req, res) => {
    req.session.destroy((err) => {
        if (err) {
            return res.json({ success: false, message: '登出失败' });
        }
        res.json({ success: true, message: '登出成功' });
    });
});

// 获取用户信息接口
app.get('/api/user', requireLogin, (req, res) => {
    res.json({ success: true, user: req.session.user });
});

// 获取学生个人信息接口
app.get('/api/student/profile', requireLogin, async (req, res) => {
    if (req.session.user.role_id !== 2) {
        return res.json({ success: false, message: '权限不足' });
    }

    try {
        // 根据用户名（学号）查询学生信息
        const rows = await executeQuery(
            'SELECT * FROM student WHERE student_id = ?',
            [req.session.user.username]
        );

        if (rows.length === 0) {
            return res.json({ success: false, message: '未找到学生信息' });
        }

        res.json({ success: true, student: rows[0] });
    } catch (error) {
        console.error('获取学生信息错误:', error);
        res.json({ success: false, message: '获取学生信息失败: ' + error.message });
    }
});

// 获取所有学生信息接口（仅管理员）
app.get('/api/students', requireLogin, async (req, res) => {
    if (req.session.user.role_id !== 3) {
        return res.json({ success: false, message: '权限不足' });
    }

    try {
        const rows = await executeQuery(`
            SELECT id, student_id, name, gender, class_name, major, create_time, update_time 
            FROM student 
            ORDER BY id
        `);

        res.json({ success: true, students: rows });
    } catch (error) {
        console.error('获取学生列表错误:', error);
        res.json({ success: false, message: '获取学生列表失败: ' + error.message });
    }
});

// 添加学生信息接口（仅管理员）
app.post('/api/students', requireLogin, async (req, res) => {
    if (req.session.user.role_id !== 3) {
        return res.json({ success: false, message: '权限不足' });
    }

    const { student_id, name, gender, class_name, major } = req.body;

    if (!student_id || !name || !gender || !class_name || !major) {
        return res.json({ success: false, message: '所有字段都是必需的' });
    }

    try {
        // 检查学号是否已存在
        const existing = await executeQuery(
            'SELECT * FROM student WHERE student_id = ?',
            [student_id]
        );

        if (existing.length > 0) {
            return res.json({ success: false, message: '学号已存在' });
        }

        // 插入新学生
        await executeQuery(
            'INSERT INTO student (student_id, name, gender, class_name, major) VALUES (?, ?, ?, ?, ?)',
            [student_id, name, gender, class_name, major]
        );

        // 同时创建用户账号
        const hashedPassword = bcrypt.hashSync('123456', 10); // 默认密码
        await executeQuery(
            'INSERT INTO user (username, password, name, role_id) VALUES (?, ?, ?, ?)',
            [student_id, hashedPassword, name, 2]
        );

        res.json({ success: true, message: '添加学生成功，默认密码为123456' });
    } catch (error) {
        console.error('添加学生错误:', error);
        res.json({ success: false, message: '添加学生失败: ' + error.message });
    }
});

// 更新学生信息接口（仅管理员）
app.put('/api/students/:id', requireLogin, async (req, res) => {
    if (req.session.user.role_id !== 3) {
        return res.json({ success: false, message: '权限不足' });
    }

    const { id } = req.params;
    const { student_id, name, gender, class_name, major } = req.body;

    if (!student_id || !name || !gender || !class_name || !major) {
        return res.json({ success: false, message: '所有字段都是必需的' });
    }

    try {
        // 获取原学号
        const oldStudent = await executeQuery(
            'SELECT student_id FROM student WHERE id = ?',
            [id]
        );

        if (oldStudent.length === 0) {
            return res.json({ success: false, message: '学生不存在' });
        }

        const oldStudentId = oldStudent[0].student_id;

        // 检查新学号是否已被其他学生使用
        if (student_id !== oldStudentId) {
            const existing = await executeQuery(
                'SELECT * FROM student WHERE student_id = ? AND id != ?',
                [student_id, id]
            );

            if (existing.length > 0) {
                return res.json({ success: false, message: '学号已被其他学生使用' });
            }
        }

        // 更新学生信息
        await executeQuery(
            'UPDATE student SET student_id = ?, name = ?, gender = ?, class_name = ?, major = ? WHERE id = ?',
            [student_id, name, gender, class_name, major, id]
        );

        // 如果学号有变化，同时更新用户表
        if (student_id !== oldStudentId) {
            await executeQuery(
                'UPDATE user SET username = ?, name = ? WHERE username = ? AND role_id = 2',
                [student_id, name, oldStudentId]
            );
        }

        res.json({ success: true, message: '更新学生信息成功' });
    } catch (error) {
        console.error('更新学生信息错误:', error);
        res.json({ success: false, message: '更新学生信息失败: ' + error.message });
    }
});

// 删除学生信息接口（仅管理员）
app.delete('/api/students/:id', requireLogin, async (req, res) => {
    if (req.session.user.role_id !== 3) {
        return res.json({ success: false, message: '权限不足' });
    }

    const { id } = req.params;

    try {
        // 获取学号
        const student = await executeQuery(
            'SELECT student_id FROM student WHERE id = ?',
            [id]
        );

        if (student.length === 0) {
            return res.json({ success: false, message: '学生不存在' });
        }

        const studentId = student[0].student_id;

        // 删除学生信息
        await executeQuery(
            'DELETE FROM student WHERE id = ?',
            [id]
        );

        // 同时删除用户账号
        await executeQuery(
            'DELETE FROM user WHERE username = ? AND role_id = 2',
            [studentId]
        );

        res.json({ success: true, message: '删除学生成功' });
    } catch (error) {
        console.error('删除学生错误:', error);
        res.json({ success: false, message: '删除学生失败: ' + error.message });
    }
});

// 获取成绩列表接口（教师和管理员）
app.get('/api/scores', requireLogin, requireTeacherOrAdmin, async (req, res) => {
    const { student_name, course_name } = req.query;

    try {
        let sql = `
            SELECT id, student_id, student_name, course_name, score, semester, create_time, update_time 
            FROM score 
            WHERE 1=1
        `;
        const params = [];

        if (student_name) {
            sql += ' AND student_name LIKE ?';
            params.push(`%${student_name}%`);
        }

        if (course_name) {
            sql += ' AND course_name LIKE ?';
            params.push(`%${course_name}%`);
        }

        sql += ' ORDER BY id DESC';

        const rows = await executeQuery(sql, params);

        res.json({ success: true, scores: rows });
    } catch (error) {
        console.error('获取成绩列表错误:', error);
        res.json({ success: false, message: '获取成绩列表失败: ' + error.message });
    }
});

// 添加成绩接口（教师和管理员）
app.post('/api/scores', requireLogin, requireTeacherOrAdmin, async (req, res) => {
    const { student_id, student_name, course_name, score, semester } = req.body;

    if (!student_id || !student_name || !course_name || !score || !semester) {
        return res.json({ success: false, message: '所有字段都是必需的' });
    }

    // 验证分数格式
    const scoreNum = parseFloat(score);
    if (isNaN(scoreNum) || scoreNum < 0 || scoreNum > 100) {
        return res.json({ success: false, message: '分数必须在0-100之间' });
    }

    try {
        // 检查学生是否存在
        const student = await executeQuery(
            'SELECT * FROM student WHERE student_id = ?',
            [student_id]
        );

        if (student.length === 0) {
            return res.json({ success: false, message: '学生不存在' });
        }

        // 插入成绩
        await executeQuery(
            'INSERT INTO score (student_id, student_name, course_name, score, semester) VALUES (?, ?, ?, ?, ?)',
            [student_id, student_name, course_name, scoreNum, semester]
        );

        res.json({ success: true, message: '添加成绩成功' });
    } catch (error) {
        console.error('添加成绩错误:', error);
        res.json({ success: false, message: '添加成绩失败: ' + error.message });
    }
});

// 更新成绩接口（教师和管理员）
app.put('/api/scores/:id', requireLogin, requireTeacherOrAdmin, async (req, res) => {
    const { id } = req.params;
    const { student_id, student_name, course_name, score, semester } = req.body;

    if (!student_id || !student_name || !course_name || !score || !semester) {
        return res.json({ success: false, message: '所有字段都是必需的' });
    }

    // 验证分数格式
    const scoreNum = parseFloat(score);
    if (isNaN(scoreNum) || scoreNum < 0 || scoreNum > 100) {
        return res.json({ success: false, message: '分数必须在0-100之间' });
    }

    try {
        // 检查成绩记录是否存在
        const existing = await executeQuery(
            'SELECT * FROM score WHERE id = ?',
            [id]
        );

        if (existing.length === 0) {
            return res.json({ success: false, message: '成绩记录不存在' });
        }

        // 更新成绩
        await executeQuery(
            'UPDATE score SET student_id = ?, student_name = ?, course_name = ?, score = ?, semester = ? WHERE id = ?',
            [student_id, student_name, course_name, scoreNum, semester, id]
        );

        res.json({ success: true, message: '更新成绩成功' });
    } catch (error) {
        console.error('更新成绩错误:', error);
        res.json({ success: false, message: '更新成绩失败: ' + error.message });
    }
});

// 删除成绩接口（教师和管理员）
app.delete('/api/scores/:id', requireLogin, requireTeacherOrAdmin, async (req, res) => {
    const { id } = req.params;

    try {
        await executeQuery(
            'DELETE FROM score WHERE id = ?',
            [id]
        );

        res.json({ success: true, message: '删除成绩成功' });
    } catch (error) {
        console.error('删除成绩错误:', error);
        res.json({ success: false, message: '删除成绩失败: ' + error.message });
    }
});

// 批量删除成绩接口
app.post('/api/scores/batch-delete', requireLogin, requireTeacherOrAdmin, async (req, res) => {
    const { ids } = req.body;

    if (!ids || !Array.isArray(ids) || ids.length === 0) {
        return res.json({ success: false, message: '请选择要删除的成绩记录' });
    }

    try {
        const placeholders = ids.map(() => '?').join(',');
        await executeQuery(
            `DELETE FROM score WHERE id IN (${placeholders})`,
            ids
        );

        res.json({ success: true, message: '批量删除成绩成功' });
    } catch (error) {
        console.error('批量删除成绩错误:', error);
        res.json({ success: false, message: '批量删除成绩失败: ' + error.message });
    }
});

// 批量导入成绩接口
app.post('/api/scores/import', requireLogin, requireTeacherOrAdmin, upload.single('file'), async (req, res) => {
    if (!req.file) {
        return res.json({ success: false, message: '请选择文件' });
    }

    try {
        let workbook;
        let data = [];

        // 解析Excel文件
        if (req.file.mimetype.includes('excel') || req.file.mimetype.includes('spreadsheet')) {
            workbook = xlsx.read(req.file.buffer, { type: 'buffer' });
            const sheetName = workbook.SheetNames[0];
            const worksheet = workbook.Sheets[sheetName];
            data = xlsx.utils.sheet_to_json(worksheet);
        }
        // 解析CSV文件
        else if (req.file.mimetype === 'text/csv') {
            const csvText = req.file.buffer.toString();
            const lines = csvText.split('\n');
            const headers = lines[0].split(',').map(h => h.trim());

            for (let i = 1; i < lines.length; i++) {
                if (lines[i].trim() === '') continue;

                const values = lines[i].split(',').map(v => v.trim());
                const row = {};
                headers.forEach((header, index) => {
                    row[header] = values[index] || '';
                });
                data.push(row);
            }
        }

        if (data.length === 0) {
            return res.json({ success: false, message: '文件中没有数据' });
        }

        // 验证数据格式
        const errors = [];
        const validScores = [];

        for (let i = 0; i < data.length; i++) {
            const row = data[i];
            const rowNumber = i + 2; // 第一行是标题

            // 检查必需字段
            if (!row.student_id || !row.student_name || !row.course_name || !row.score || !row.semester) {
                errors.push(`第${rowNumber}行: 缺少必需字段`);
                continue;
            }

            // 验证分数
            const scoreNum = parseFloat(row.score);
            if (isNaN(scoreNum) || scoreNum < 0 || scoreNum > 100) {
                errors.push(`第${rowNumber}行: 分数格式不正确 (${row.score})`);
                continue;
            }

            // 检查学生是否存在
            const student = await executeQuery(
                'SELECT * FROM student WHERE student_id = ?',
                [row.student_id]
            );

            if (student.length === 0) {
                errors.push(`第${rowNumber}行: 学生不存在 (学号: ${row.student_id})`);
                continue;
            }

            validScores.push({
                student_id: row.student_id,
                student_name: row.student_name,
                course_name: row.course_name,
                score: scoreNum,
                semester: row.semester
            });
        }

        // 如果有错误，返回错误信息
        if (errors.length > 0) {
            return res.json({
                success: false,
                message: `导入失败，发现 ${errors.length} 个错误`,
                errors: errors
            });
        }

        // 插入有效数据
        for (const score of validScores) {
            await executeQuery(
                'INSERT INTO score (student_id, student_name, course_name, score, semester) VALUES (?, ?, ?, ?, ?)',
                [score.student_id, score.student_name, score.course_name, score.score, score.semester]
            );
        }

        res.json({
            success: true,
            message: `成功导入 ${validScores.length} 条成绩记录`
        });
    } catch (error) {
        console.error('导入成绩错误:', error);
        res.json({ success: false, message: '导入成绩失败: ' + error.message });
    }
});

// 获取用户列表接口（仅管理员）
app.get('/api/users', requireLogin, async (req, res) => {
    if (req.session.user.role_id !== 3) {
        return res.json({ success: false, message: '权限不足' });
    }

    try {
        const rows = await executeQuery(`
            SELECT id, username, name, role_id, create_time, update_time 
            FROM user 
            ORDER BY id
        `);

        res.json({ success: true, users: rows });
    } catch (error) {
        console.error('获取用户列表错误:', error);
        res.json({ success: false, message: '获取用户列表失败: ' + error.message });
    }
});

// 获取查询设置接口
app.get('/api/query-settings', requireLogin, async (req, res) => {
    try {
        const rows = await executeQuery(`
            SELECT qs.*, u.name as creator_name 
            FROM query_settings qs 
            LEFT JOIN user u ON qs.created_by = u.id 
            ORDER BY qs.id DESC 
            LIMIT 1
        `);

        if (rows.length > 0) {
            res.json({ success: true, settings: rows[0] });
        } else {
            res.json({ success: true, settings: null });
        }
    } catch (error) {
        console.error('获取查询设置错误:', error);
        res.json({ success: false, message: '获取查询设置失败: ' + error.message });
    }
});

// 更新查询设置接口（仅教师和管理员）
app.post('/api/query-settings', requireLogin, requireTeacherOrAdmin, async (req, res) => {
    const { start_time, end_time } = req.body;

    if (!start_time || !end_time) {
        return res.json({ success: false, message: '开始时间和结束时间都是必需的' });
    }

    const startTime = new Date(start_time);
    const endTime = new Date(end_time);

    if (startTime >= endTime) {
        return res.json({ success: false, message: '结束时间必须晚于开始时间' });
    }

    try {
        // 插入新的查询设置
        await executeQuery(
            'INSERT INTO query_settings (start_time, end_time, created_by) VALUES (?, ?, ?)',
            [start_time, end_time, req.session.user.id]
        );

        res.json({ success: true, message: '查询时间段设置成功' });
    } catch (error) {
        console.error('设置查询时间错误:', error);
        res.json({ success: false, message: '设置查询时间失败: ' + error.message });
    }
});

// 获取学生成绩接口（带时间验证）
app.get('/api/student/scores', requireLogin, async (req, res) => {
    if (req.session.user.role_id !== 2) {
        return res.json({ success: false, message: '权限不足' });
    }

    try {
        // 检查当前查询设置
        const settings = await executeQuery(`
            SELECT * FROM query_settings 
            ORDER BY id DESC 
            LIMIT 1
        `);

        // 如果有查询设置，检查当前时间是否在允许查询的时间段内
        if (settings.length > 0) {
            const now = new Date();
            const startTime = new Date(settings[0].start_time);
            const endTime = new Date(settings[0].end_time);

            if (now < startTime || now > endTime) {
                return res.json({
                    success: false,
                    message: `当前不在成绩查询时间段内。查询时间：${formatDateTime(startTime)} 至 ${formatDateTime(endTime)}`
                });
            }
        }

        // 获取学生成绩
        const rows = await executeQuery(
            'SELECT * FROM score WHERE student_id = ? ORDER BY semester DESC, course_name ASC',
            [req.session.user.username]
        );

        res.json({ success: true, scores: rows });
    } catch (error) {
        console.error('获取学生成绩错误:', error);
        res.json({ success: false, message: '获取成绩失败: ' + error.message });
    }
});

// 辅助函数：格式化日期时间（精确到分钟）
function formatDateTime(date) {
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0');
    const day = String(date.getDate()).padStart(2, '0');
    const hours = String(date.getHours()).padStart(2, '0');
    const minutes = String(date.getMinutes()).padStart(2, '0');
    return `${year}-${month}-${day} ${hours}:${minutes}`;
}

// 主页面 - 根据角色显示不同界面
app.get('/main', requireLogin, (req, res) => {
    res.sendFile(path.join(__dirname, 'main.html'));
});

// 个人信息页面
app.get('/profile.html', requireLogin, (req, res) => {
    res.sendFile(path.join(__dirname, 'public', 'profile.html'));
});

// 学生信息管理页面
app.get('/student_management.html', requireLogin, (req, res) => {
    if (req.session.user.role_id !== 3) {
        return res.redirect('/main');
    }
    res.sendFile(path.join(__dirname, 'public', 'student_management.html'));
});

// 成绩管理页面
app.get('/score_management.html', requireLogin, (req, res) => {
    if (req.session.user.role_id === 2) {
        return res.redirect('/main');
    }
    res.sendFile(path.join(__dirname, 'public', 'score_management.html'));
});

// 成绩查询页面
app.get('/score_query.html', requireLogin, (req, res) => {
    res.sendFile(path.join(__dirname, 'public', 'score_query.html'));
});

// 查询设置页面
app.get('/settings.html', requireLogin, (req, res) => {
    if (req.session.user.role_id === 2) {
        return res.redirect('/main');
    }
    res.sendFile(path.join(__dirname, 'public', 'settings.html'));
});

// 根路径重定向到登录页面
app.get('/', (req, res) => {
    if (req.session.user) {
        return res.redirect('/main');
    }
    res.sendFile(path.join(__dirname, 'index.html'));
});

// 健康检查端点
app.get('/health', async (req, res) => {
    const dbStatus = await checkDatabaseConnection();
    res.json({
        status: dbStatus ? 'healthy' : 'unhealthy',
        database: dbStatus ? 'connected' : 'disconnected',
        timestamp: new Date().toISOString()
    });
});

// 启动服务器
app.listen(PORT, async () => {
    console.log(`服务器运行在 http://47.109.142.41:${PORT}`);
    //console.log(`数据库配置: ${dbConfig.host}:${dbConfig.port}/${dbConfig.database}`);

    // 初始健康检查
    const dbHealthy = await checkDatabaseConnection();
    if (dbHealthy) {
        console.log('系统启动完成，所有服务正常运行');
    } else {
        console.log('系统启动完成，但数据库连接异常');
    }
});

// 优雅关闭处理
process.on('SIGINT', async () => {
    console.log('正在关闭服务器...');
    try {
        await pool.end();
        console.log('数据库连接池已关闭');
        process.exit(0);
    } catch (error) {
        console.error('关闭数据库连接池时出错:', error);
        process.exit(1);
    }
});

module.exports = app;